package br.unicentro.lynx.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import br.unicentro.lynx.model.factory.ConnectionFactory;
import br.unicentro.lynx.model.pojo.Contact;

public class ContactDao {
	private String sql;
	private Connection connection;
	private PreparedStatement statement;
	private ResultSet result;

	public void insert(Contact contact) {
		open();
		this.sql = "INSERT INTO Contacts (street_contact, number_contact, zip_code_contact, complement_contact, city_contact, state_contact, phone_contact, email_contact) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			this.statement = connection.prepareStatement(sql);
			this.statement.setString(1, contact.getStreetContact());
			this.statement.setInt(2, contact.getNumberContact());
			this.statement.setString(3, contact.getZipCodeContact());
			this.statement.setString(4, contact.getComplementContact());
			this.statement.setString(5, contact.getCityContact());
			this.statement.setString(6, contact.getStateContact());
			this.statement.setString(7, contact.getPhoneContact());
			this.statement.setString(8, contact.getEmailContact());
			this.statement.execute();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			close();
		}
	}

	public void update(Contact contact) {
		open();
		this.sql = "UPDATE Contacts SET street_contact = ?, number_contact = ?, zip_code_contact = ?, complement_contact = ?, city_contact = ?, state_contact = ?, phone_contact = ?, email_contact = ? WHERE id_contact = ?";
		try {
			this.statement = connection.prepareStatement(sql);
			this.statement.setString(1, contact.getStreetContact());
			this.statement.setInt(2, contact.getNumberContact());
			this.statement.setString(3, contact.getZipCodeContact());
			this.statement.setString(4, contact.getComplementContact());
			this.statement.setString(5, contact.getCityContact());
			this.statement.setString(6, contact.getStateContact());
			this.statement.setString(7, contact.getPhoneContact());
			this.statement.setString(8, contact.getEmailContact());
			this.statement.setInt(9, contact.getIdContact());
			this.statement.execute();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			close();
		}
	}

	public void delete(Contact contact) {
		open();
		this.sql = "DELETE FROM Contacts WHERE id_contact = ?";
		try {
			this.statement = connection.prepareStatement(sql);
			this.statement.setInt(1, contact.getIdContact());
			this.statement.execute();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			close();
		}
	}

	public Contact search(Integer id_contact) {
		open();
		this.sql = "SELECT * FROM Contacts WHERE id_contact = ?";
		try {
			this.statement = connection.prepareStatement(sql);
			this.statement.setInt(1, id_contact);
			this.result = this.statement.executeQuery();
			if (this.result.next()) {
				return new Contact(result.getInt("id_contact"),
						result.getString("street_contact"),
						result.getInt("number_contact"),
						result.getString("zip_code_contact"),
						result.getString("complement_contact"),
						result.getString("city_contact"),
						result.getString("state_contact"),
						result.getString("phone_contact"),
						result.getString("email_contact"));
			} else {
				return null;
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			close();
		}
	}

	public ArrayList<Contact> list() {
		open();
		this.sql = "SELECT * FROM Contact";
		ArrayList<Contact> contacts = new ArrayList<Contact>();
		try {
			this.statement = connection.prepareStatement(sql);
			this.result = this.statement.executeQuery();
			while (this.result.next()) {
				contacts.add(new Contact(result.getInt("id_contact"), result
						.getString("street_contact"), result
						.getInt("number_contact"), result
						.getString("zip_code_contact"), result
						.getString("complement_contact"), result
						.getString("city_contact"), result
						.getString("state_contact"), result
						.getString("phone_contact"), result
						.getString("email_contact")));
			}
			return contacts;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			try {
				this.result.close();
				close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

	private void open() {
		this.connection = ConnectionFactory.getConnection(
				ConnectionFactory.URL, ConnectionFactory.USER,
				ConnectionFactory.PASSWORD);
	}

	private void close() {
		try {
			this.statement.close();
			this.connection.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
}
